package app

import (
	"fmt"
	"os"
	"path/filepath"
	"strconv"
	"strings"

	"git.sr.ht/~mil/mobsql/dbschema"
	"git.sr.ht/~mil/mobsql/logger"
	"git.sr.ht/~mil/mobsql/sqlgen"
	"git.sr.ht/~mil/mobsql/utildownload"
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

// InitializeApp does 3 primary things:
// 1) Opens the SQLite database - setting pragmas etc according
// 2) Runs schema creation logic (e.g. create tables)
// 3) Fetches from HTTP the Mobility Database CSV file & imports to _mdb table
//
// The database connection & schema are grouped into the MDB struct and
// returned such that other public fns in this sourcefile may be run against
// MDB.
func InitializeApp(path string) (*MDB, error) {
	var (
		mobilityDBURL = "https://bit.ly/catalogs-csv"
		mdb           = MDB{Schema: dbschema.Schema}
		err           error
	)

	if err = os.MkdirAll(filepath.Dir(path), os.ModePerm); err != nil {
		return nil, err
	} else if mdb.DB, err = sqlx.Open("sqlite3", path); err != nil {
		return nil, err
	} else if sqliteVersion, err := mdb.QueryValue("select sqlite_version()", []any{}); err != nil {
		return nil, err
	} else if err := versionGTCheck("3.28.0", sqliteVersion); err != nil {
		return nil, fmt.Errorf("SQLite version error: %v", err)
	} else if _, err = mdb.DB.Exec(`
		pragma encoding = 'UTF-8';
		pragma auto_vaccum = 1;
		pragma journal_mode = wal;
		pragma synchronous = off;

		-- Note in regards to memory temp_store:
		--   Android and certain platforms have different restrictions on
		--   temporary directory size limits. For android specifically, it seems
		--   like large inserts such as creating computed tables (like insert
		--   into x select from y) can quickly overrun small temporary dir space.
		--   Using memory temp_store seems to resolve this; in the future this
		--   may also be able to be resolved by using a custom temp directory
		--   via the 'temp_store_directory' pragma, but this is simpler and
		--   does the job for now. Without this fix, large insert fail and
		--   throw RO errors.
		pragma temp_store = memory;
	`); err != nil {
		return nil, err
	} else if err = mdb.schemaInit(); err != nil {
		return nil, err
	} else if file, _, err := utildownload.DownloadURLCached(mobilityDBURL, ".csv"); err != nil {
		return nil, err
	} else if err = mdb.importTableFromSpec(
		&sqlgen.LoadTable{File: file, TableSpec: mdb.Schema.TableSystemMDB},
		nil,
		"",
		-1,
	); err != nil {
		return nil, err
	} else {
		return &mdb, nil
	}
}

// schemaInit initializes the SQLite database in 3 steps: 1) creation
// of internal tracking tables, 2) creation of GTFS tables, and 3) creation
// of views
func (m *MDB) schemaInit() error {
	if err := m.schemaInitCreateTables(); err != nil {
		return err
	} else if err := m.schemaInitCreateTablesIndexes(); err != nil {
		return err
	} else if err := m.schemaInitViews(); err != nil {
		return err
	} else {
		return nil
	}
}

// schemaInitCreateTablesGTFS create the GTFS tables loaded from the schema
func (m *MDB) schemaInitCreateTables() error {
	logger.Warn("Loading schema tables")
	defer logger.Warn("Loading schema tables ok")

	// Generate create statements from schema's table definitions
	createStatements := []string{}
	for _, tbl := range []sqlgen.TableSpec{m.Schema.TableSystemTracking, m.Schema.TableSystemMDB} {
		createStatements = append(createStatements, sqlgen.GenerateLoadTableSpecCreateSQL(&tbl))
	}
	for _, table := range m.Schema.TablesGTFS {
		createStatements = append(createStatements, sqlgen.GenerateLoadTableSpecCreateSQL(&table.TableSpec))
	}
	for _, table := range m.Schema.TablesComputed {
		createStatements = append(createStatements, sqlgen.GenerateLoadTableSpecCreateSQL(&table.TableSpec))
	}

	// Execute create statements
	for _, statement := range createStatements {
		logger.Warnf("Execute statement:\n%s", statement)
		if _, err := m.DB.Exec(statement); err != nil {
			return fmt.Errorf("%v: Failed to execute statement", err)
		}
	}
	return nil
}

// schemaInitCreateTablesGTFS create the GTFS tables loaded from the schema
func (m *MDB) schemaInitCreateTablesIndexes() error {
	logger.Warn("Loading schema indexes")
	defer logger.Warn("Loading schema indexes ok")

	indexCreateFn := func(tableName string, indexFields []string) error {
		//table.Indexes[i] = append(table.Indexes[i], "source")
		indexName, createIndexSQL := sqlgen.GenerateLoadTableSpecIndexSQL(tableName, indexFields)

		logger.Warnf("Create index %s on %s as %v", indexName, tableName, indexFields)
		if n, err := m.QueryValue("select count(*) from sqlite_master where type='index' and name= ?", []any{indexName}); err != nil {
			return err
		} else if n != "0" {
			logger.Warnf("Skipping creation of already existing index %s", indexName)
			return nil
		} else if _, err := m.DB.Exec(createIndexSQL); err != nil {
			return fmt.Errorf("%v: Failed to execute statement", err)
		} else {
			logger.Warnf("Created index %s ok", indexName)
			return nil
		}
	}

	for _, table := range m.Schema.TablesGTFS {
		for i, _ := range table.TableSpec.Indexes {
			if err := indexCreateFn(table.TableSpec.Table, table.TableSpec.Indexes[i]); err != nil {
				return err
			}
		}
	}
	for _, table := range m.Schema.TablesComputed {
		for i, _ := range table.TableSpec.Indexes {
			if err := indexCreateFn(table.TableSpec.Table, table.TableSpec.Indexes[i]); err != nil {
				return err
			}
		}
	}

	return nil
}

// schemaInitViews initializes views for the schema
func (m *MDB) schemaInitViews() error {
	logger.Warn("Loading views ok")
	defer logger.Warn("Loading views ok")

	for _, view := range m.Schema.Views {
		if viewSelectBytes, err := dbschema.EmbedFS.ReadFile(view.SQLSelectFile); err != nil {
			return err
		} else {
			createView := fmt.Sprintf("drop view if exists %s; create view %s as %s", view.Name, view.Name, string(viewSelectBytes))
			if _, err := m.DB.Exec(createView); err != nil {
				return fmt.Errorf("%v: Failed to execute statement", err)
			}
			logger.Warnf("Loaded view %s from file: %s (bytes=%d)", view.Name, view.SQLSelectFile, len(viewSelectBytes))
		}
	}
	return nil
}

func versionGTCheck(expectVersion, hasVersion string) error {
	var (
		expectSplit    = strings.Split(expectVersion, ".")
		hasSplit       = strings.Split(hasVersion, ".")
		expectMajor, _ = strconv.Atoi(expectSplit[0])
		expectMinor, _ = strconv.Atoi(expectSplit[1])
		expectPatch, _ = strconv.Atoi(expectSplit[2])
		hasMajor, _    = strconv.Atoi(hasSplit[0])
		hasMinor, _    = strconv.Atoi(hasSplit[1])
		hasPatch, _    = strconv.Atoi(hasSplit[2])
	)

	logger.Warnf("SQLite system version (%s); comparing to required version: %s", hasVersion, expectVersion)
	if hasMajor >= expectMajor && hasMinor >= expectMinor && hasPatch >= expectPatch {
		return nil
	} else {
		return fmt.Errorf("Has version %s is not greater or equal to required version %s", hasVersion, expectVersion)
	}
}
